package elicecn.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.HanyuPinyinVCharType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class CovnertXls {
	static final String sql = "#MOBILE_NUMBER#,#USER_NAME#,#USER_CORP#,#USER_DEPT#,#USER_JC#,#LOCATION#\r\n";
	private List<String> list;

	public void readXls(String fileIn, String fileOut)
			throws FileNotFoundException, IOException,
			BadHanyuPinyinOutputFormatCombination {
		list = new ArrayList<String>();

		HanyuPinyinOutputFormat hanYuPinOutputFormat = new HanyuPinyinOutputFormat();
		hanYuPinOutputFormat.setCaseType(HanyuPinyinCaseType.UPPERCASE);
		hanYuPinOutputFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
		hanYuPinOutputFormat.setVCharType(HanyuPinyinVCharType.WITH_V);

		HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(fileIn));
		HSSFSheet sheet = wb.getSheetAt(0);
		int rows = sheet.getPhysicalNumberOfRows();
		for (int i = 1; i < rows; i++) {
			String csql = sql;
			HSSFRow row = sheet.getRow(i);
			HSSFCell mpc = row.getCell(0);// 电话
			if (mpc != null) {
				if (mpc.getCellType() == HSSFCell.CELL_TYPE_STRING) {
					csql = csql.replace("#MOBILE_NUMBER#", mpc
							.getStringCellValue());
				} else if (mpc.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
					csql = csql.replace("#MOBILE_NUMBER#", String.valueOf((long)mpc
							.getNumericCellValue()));
				}
			}
			HSSFCell uc = row.getCell(1);// 姓名
			if (uc != null) {
				System.out.println(i);
				String uname, dept, sname;
				String val = uc.getStringCellValue();
				// 姓名
				int idx = val.indexOf("(");
				if (idx == -1) {
					idx = val.indexOf("（");
				}
				uname = idx == -1 ? "" : val.substring(0, idx).trim();
				csql = csql.replace("#USER_NAME#", uname);
				// 部门
				int idx2 = val.lastIndexOf("(");
				if (idx2 == -1) {
					idx2 = val.lastIndexOf("（");
				}
				dept = idx2 == -1 ? "" : val.substring(idx2 + 1);
				int idx3 = val.lastIndexOf(")");
				if (idx3 == -1) {
					idx3 = val.lastIndexOf("）");
				}
				dept = idx3 != -1 && dept.length() > 0 ? dept.substring(0, dept
						.length() - 1) : dept;
				// System.out.println(dept);
				csql = csql.replace("#USER_DEPT#", dept);
				// 简称
				if (uname.length() <= 4) {
					char[] nameArray = uname.toCharArray();
					StringBuffer buf = new StringBuffer();
					for (int j = 0; j < nameArray.length; j++) {
						String[] py = PinyinHelper.toHanyuPinyinStringArray(
								nameArray[j], hanYuPinOutputFormat);
//						if (py.length < 2) {
							buf.append(py[0].charAt(0));
//						} else {
//							buf.append(py[1].charAt(0));
//						}
					}
					sname = buf.toString();
				} else {
					sname = "";
				}
				System.out.println(uname + sname);
				csql = csql.replace("#USER_JC#", sname);
			}
			HSSFCell cc = row.getCell(2);// 公司
			if (cc != null) {
				csql = csql.replace("#USER_CORP#", cc.getStringCellValue());
			}
			HSSFCell areac = row.getCell(9);// 地域
			if (areac != null) {
				csql = csql.replace("#LOCATION#", areac.getStringCellValue());
			}
			// System.out.println(csql);
			list.add(csql);
		}
		FileWriter writer = new FileWriter(new File(fileOut));
		for (String str : list) {
			writer.write(str);
		}
		writer.close();
	}

	public static void main(String[] args) throws FileNotFoundException,
			IOException, BadHanyuPinyinOutputFormatCombination {
		new CovnertXls().readXls("E:/[BONC]/需求文档/20100524数据转换/Book1.xls",
				"E:/[BONC]/需求文档/20100524数据转换/sBook.txt");
	}
}
